﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using SAPbobsCOM;

namespace SBOSynOITM
{
    public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }
        System.Timers.Timer timer1;  //计时器
        private bool isExcu = false;
        private static DateTime update = DateTime.Today;
        private string path0 = AppDomain.CurrentDomain.BaseDirectory + @"\Date";
        public static SAPbobsCOM.Company oCompany;
        private string Error;
        private string Success;
        protected override void OnStart(string[] args)
        {

            System.Timers.Timer timer = new System.Timers.Timer(60000); //间隔10秒

            timer.Enabled = true;  //执行一次
            timer.Elapsed += new ElapsedEventHandler(ExecutionCode);
            // timer.Start();
            timer.AutoReset = true;


            //timer1 = new System.Timers.Timer();
            //timer1.Interval = 60000;  //设置计时器事件间隔执行时间
            //if (DateTime.Now.ToString("HH:mm") == "23:00")
            //{

            //    timer1.Elapsed += new System.Timers.ElapsedEventHandler(timer1_Elapsed);
            //    timer1.Enabled = true;
            //}

        }

        private string CkUpdateDate()
        {
            string oSql = "SELECT DISTINCT [ItemCode] FROM [cdc].[dbo_OITM_CT] WHERE [__$operation] IN (2,4)";
            DataTable oDt =
                GetDtFromSQL(oSql);
            if (oDt != null && oDt.Rows.Count > 0)
            {
                AddTextTofile(DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + ":执行了" + oDt.Rows.Count + "条数据\n", @"D:/ExecutionService.txt");
                return oSql;
            }
            else
            {
                return "";
            }
        }
        private void AddTextTofile(string txt, string FilePath)
        {
            string path = FilePath;
            if (!File.Exists(path))
            {
                FileInfo myfile = new FileInfo(path);
                FileStream fs = myfile.Create();
                fs.Close();
            }
            StreamWriter sw = File.AppendText(path);
            sw.WriteLine(txt);
            sw.Flush();
            sw.Close();
        }

        private void ExecutionCode(object source, System.Timers.ElapsedEventArgs e)
        {
            // string dtNow = DateTime.Now.ToString("HH:mm");
            if (isExcu == false)
            {
                SYS();

                // File.WriteAllText("D:/ExecutionService.txt", "服务执行了一次任务", Encoding.UTF8);
            }
        }
        protected override void OnStop()
        {
            try
            {
                this.timer1.Enabled = false;
            }
            catch
            {


            }
        }
        private void SYS()
        {
            isExcu = true;
            //string x = Read(path0);
            //if (string.IsNullOrEmpty(x))
            //{
            //    update = DateTime.Parse("1900-01-01");
            //}
            //else
            //{
            //    update = DateTime.Parse(x);
            //}
            SysOITMNow();
            //SysMJReport();
            isExcu = false;
            //执行SQL语句或其他操作
        }
        private void SysOITMNow()
        {
            Error = "";
            Success = "";
            try
            {
                string osup = CkUpdateDate();
                if (!string.IsNullOrEmpty(osup))
                {
                    if (Connect2SBO())
                    {
                        SynOITM(osup);
                    }
                    else
                    {
                        Error += "登录B1失败，请确认网络连接正常！";
                    }

                }
            }
            catch (Exception ex)
            {

                Error += ex.ToString();
            }
            finally
            {
                if (Error != "")
                {
                    string path = AppDomain.
                                      CurrentDomain.BaseDirectory + @"\ErrorLog\" +
                                  DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt";
                    WriteLog(Error, path);
                    //     System.Diagnostics.Process.Start(path);
                }
                if (Success != "")
                {
                    string path = AppDomain.
                                     CurrentDomain.BaseDirectory + @"\Success\" +
                                 DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt";
                    WriteLog(Success, path);
                }
            }

        }

        private void SynOITM(string oSQLupdate)
        {
            string oSQL = "SELECT oitm.*,oitb.u_sysflag as grpcode FROM oitm LEFT JOIN oitb ON OITB.ItmsGrpCod = OITM.ItmsGrpCod where ItemCode in (" + oSQLupdate + ")"; // "SELECT oitm.*,oitb.u_sysflag as grpcode FROM oitm LEFT JOIN oitb ON OITB.ItmsGrpCod = OITM.ItmsGrpCod WHERE oitm.U_Sysflag = 1 or( (oitm.CreateDate>='" + update.ToShortDateString() + "' OR oitm.UpdateDate>='" + update.ToShortDateString() + "'))";
            DataTable oDt =
                GetDtFromSQL(oSQL);
            List<string> oCol = new List<string>();
            for (int i = 0; i < oDt.Columns.Count; i++)
            {
                if (oDt.Columns[i].ColumnName.StartsWith("U_"))
                {
                    oCol.Add(oDt.Columns[i].ColumnName);
                }
            }
            //  progressBar1.Maximum = oDt.Rows.Count;
            int x = 1;
            foreach (DataRow row in oDt.Rows)
            {
                x++;
                //progressBar1.Step = 1;
                //progressBar1.Value = x++;
                SAPbobsCOM.Items oItems = (SAPbobsCOM.Items)oCompany.GetBusinessObject(BoObjectTypes.oItems);
                string ItemCode = row.Field<string>("ItemCode").Trim();
                string ItemName = row.Field<string>("ItemName");
                string FrgnName = row.Field<string>("FrgnName");
                int ItmsGrpCod = row.Field<int>("grpcode");
                string DfltWH = row.Field<string>("DfltWH");
                string CardCode = "CDMJSSJ";//row.Field<string>("CardCode");//
                string PrchseItem = row.Field<string>("PrchseItem");
                string SellItem = row.Field<string>("SellItem");
                string InvntItem = row.Field<string>("InvntItem");
                string VatGourpSa = row.Field<string>("VatGourpSa");
                string IncomeAcct = row.Field<string>("IncomeAcct");
                string SuppCatNum = row.Field<string>("SuppCatNum");
                string BuyUnitMsr = row.Field<string>("BuyUnitMsr");
                string SalUnitMsr = row.Field<string>("SalUnitMsr");
                string EvalSystem = row.Field<string>("EvalSystem");
                int FirmCode = row.Field<short>("FirmCode");
                string QryGroup1 = row.Field<string>("QryGroup1");
                string QryGroup2 = row.Field<string>("QryGroup2");
                string QryGroup3 = row.Field<string>("QryGroup3");
                string VatGroupPu = row.Field<string>("VatGroupPu");
                string ExpensAcct = row.Field<string>("ExpensAcct");
                string FrgnInAcct = row.Field<string>("FrgnInAcct");
                int? ShipType = row.Field<short?>("ShipType");
                string GLMethod = row.Field<string>("GLMethod");
                string ByWh = row.Field<string>("ByWh");
                string BaseUnit = row.Field<string>("BaseUnit");
                string InvntryUom = row.Field<string>("InvntryUom");
                string TaxCodeAR = row.Field<string>("TaxCodeAR");
                string TaxCodeAP = row.Field<string>("TaxCodeAP");
                if (oItems.GetByKey(ItemCode))
                {
                    oItems.ItemName = ItemName;
                    oItems.ForeignName = FrgnName;
                    oItems.ItemsGroupCode = ItmsGrpCod;
                    oItems.DefaultWarehouse = DfltWH;
                    oItems.Mainsupplier = CardCode;
                    oItems.PurchaseItem = PrchseItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.SalesItem = SellItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.InventoryItem = InvntItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.SalesVATGroup = VatGourpSa;
                    oItems.IncomeAccount = IncomeAcct;
                    oItems.SupplierCatalogNo = SuppCatNum;
                    oItems.PurchaseUnit = BuyUnitMsr;
                    oItems.SalesUnit = SalUnitMsr;
                    oItems.CostAccountingMethod = BoInventorySystem.bis_MovingAverage;
                    oItems.Manufacturer = FirmCode;
                    oItems.set_Properties(1, QryGroup1 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.set_Properties(2, QryGroup2 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.set_Properties(3, QryGroup3 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.PurchaseVATGroup = VatGroupPu;
                    oItems.ExpanseAccount = ExpensAcct;
                    oItems.ForeignRevenuesAccount = FrgnInAcct;
                    if (ShipType.HasValue)
                    {
                        oItems.ShipType = ShipType.Value;
                    }

                    switch (GLMethod)
                    {
                        case "C":
                            oItems.GLMethod = BoGLMethods.glm_ItemClass;
                            break;
                        case "W":
                            oItems.GLMethod = BoGLMethods.glm_WH;
                            break;
                        case "L":
                            oItems.GLMethod = BoGLMethods.glm_ItemLevel;
                            break;
                    }
                    oItems.ManageStockByWarehouse = ByWh == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.BaseUnitName = BaseUnit;
                    oItems.InventoryUOM = InvntryUom;
                    oItems.ArTaxCode = TaxCodeAR;
                    oItems.ApTaxCode = TaxCodeAP;
                    foreach (string col in oCol)
                    {
                        try
                        {
                            oItems.UserFields.Fields.Item(col).Value = row[col].ToString().Trim();

                        }
                        catch (Exception ex)
                        {
                            Error += "'" + oItems.ItemCode + "'" + "目标账套没有自定义字段:" + col + "\r\n";
                        }
                    }
                    if (0 == oItems.Update())
                    {
                        Success += "'" + oItems.ItemCode + "'更新成功！\r\n";
                        // UpdateFlag(ItemCode);
                        DeleteCDC(ItemCode);
                    }
                    else
                    {
                        Error += "'" + oItems.ItemCode + "'" + oCompany.GetLastErrorDescription() + "\r\n";
                    }
                }
                else
                {
                    oItems = (SAPbobsCOM.Items)oCompany.GetBusinessObject(BoObjectTypes.oItems);
                    oItems.ItemCode = ItemCode;
                    oItems.ItemName = ItemName;
                    oItems.ForeignName = FrgnName;
                    oItems.ItemsGroupCode = ItmsGrpCod;
                    oItems.DefaultWarehouse = DfltWH;
                    oItems.Mainsupplier = CardCode;
                    oItems.PurchaseItem = PrchseItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.SalesItem = SellItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.InventoryItem = InvntItem == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.SalesVATGroup = VatGourpSa;
                    oItems.IncomeAccount = IncomeAcct;
                    oItems.SupplierCatalogNo = SuppCatNum;
                    oItems.PurchaseUnit = BuyUnitMsr;
                    oItems.SalesUnit = SalUnitMsr;
                    oItems.CostAccountingMethod = BoInventorySystem.bis_MovingAverage;
                    oItems.Manufacturer = FirmCode;
                    oItems.set_Properties(1, QryGroup1 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.set_Properties(2, QryGroup2 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.set_Properties(3, QryGroup3 == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO);
                    oItems.PurchaseVATGroup = VatGroupPu;
                    oItems.ExpanseAccount = ExpensAcct;
                    oItems.ForeignRevenuesAccount = FrgnInAcct;
                    if (ShipType.HasValue)
                    {
                        oItems.ShipType = ShipType.Value;
                    }
                    switch (GLMethod)
                    {
                        case "C":
                            oItems.GLMethod = BoGLMethods.glm_ItemClass;
                            break;
                        case "W":
                            oItems.GLMethod = BoGLMethods.glm_WH;
                            break;
                        case "L":
                            oItems.GLMethod = BoGLMethods.glm_ItemLevel;
                            break;
                    }
                    oItems.ManageStockByWarehouse = ByWh == "Y" ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
                    oItems.BaseUnitName = BaseUnit;
                    oItems.InventoryUOM = InvntryUom;
                    oItems.ArTaxCode = TaxCodeAR;
                    oItems.ApTaxCode = TaxCodeAP;
                    foreach (string col in oCol)
                    {
                        try
                        {
                            oItems.UserFields.Fields.Item(col).Value = row[col].ToString().Trim();
                        }
                        catch (Exception ex)
                        {
                            //   Error += ex.ToString();
                        }
                    }
                    if (oItems.Add() == 0)
                    {
                        Success += "'" + oItems.ItemCode + "'添加成功！\r\n";
                        // UpdateFlag(ItemCode);
                        DeleteCDC(ItemCode);
                        //MessageBox.Show("");
                    }
                    else
                    {
                        Error += "'" + oItems.ItemCode + "'" + oCompany.GetLastErrorDescription() + "\r\n";
                        //              MessageBox.Show(oCompany.GetLastErrorDescription());
                    }
                }

            }
            update = DateTime.Today;
            WriteLogNew(update.ToShortDateString(), path0);

        }
        private bool Connect2SBO()
        {
            oCompany = new Company();
            oCompany.LicenseServer = "192.168.2.22:30000";
            oCompany.DbServerType = BoDataServerTypes.dst_MSSQL2012;
            oCompany.DbUserName = "sa";
            oCompany.DbPassword = "abcd=1234";
            oCompany.Server = "192.168.2.26";
            oCompany.UserName = "manager";
            oCompany.Password = "isap";
            oCompany.CompanyDB = "MJ_Project";

            //oCompany.LicenseServer = "WIN-24JVG30P5AG";
            //oCompany.DbServerType = BoDataServerTypes.dst_MSSQL2008;
            //oCompany.DbUserName = "sa";
            //oCompany.DbPassword = "sa";
            //oCompany.Server = "127.0.0.1";
            //oCompany.UserName = "manager";
            //oCompany.Password = "isap";
            //oCompany.CompanyDB = "Best002";
            if (oCompany.Connect() == 0)
            {
                return true;
                //MessageBox.Show("连接成功!");
            }
            else
            {
                Error += oCompany.GetLastErrorDescription() + "\n";
                return false;
                // MessageBox.Show(oCompany.GetLastErrorDescription());
            }

        }
        private DataTable GetDtFromSQL(string oSQL)
        {

            SqlConnection conn = new SqlConnection("SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;");
            // SqlConnection conn = new SqlConnection("SERVER=.;DATABASE=Best003;PWD=sa;UID=sa;");
            SqlCommand cmd = new SqlCommand(oSQL, conn);
            DataTable dt = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            conn.Close();
            return dt;

        }
        private DataTable GetDtFromSQL(string oSQL, string ConStr)
        {
            SqlConnection conn = new SqlConnection(ConStr);
            //SqlConnection conn = new SqlConnection("SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;");
            // SqlConnection conn = new SqlConnection("SERVER=.;DATABASE=Best003;PWD=sa;UID=sa;");
            SqlCommand cmd = new SqlCommand(oSQL, conn);
            DataTable dt = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            conn.Close();
            return dt;

        }


        private void DeleteCDC(string ItemCode)
        {
            //先打开两个类库文件
            SqlConnection con = new SqlConnection();
            //  con.ConnectionString = "server=505-03;database=ttt;user=sa;pwd=123";
            con.ConnectionString = "SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;";
            // con.ConnectionString = "SERVER=.;DATABASE=Best003;PWD=sa;UID=sa;";
            con.Open();
            /*
            SqlDataAdapter 对象。 用于填充DataSet （数据集）。
            SqlDataReader 对象。 从数据库中读取流..
            后面要做增删改查还需要用到 DataSet 对象。
            */

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "DELETE FROM [cdc].[dbo_OITM_CT] WHERE [ItemCode] = '" + ItemCode + "'";
            SqlDataReader dr = com.ExecuteReader();//执行SQL语句
            dr.Close();//关闭执行
            con.Close();//关闭数据库
        }
        private void UpdateFlag(string itemcode)
        {
            //先打开两个类库文件
            SqlConnection con = new SqlConnection();
            //  con.ConnectionString = "server=505-03;database=ttt;user=sa;pwd=123";
            con.ConnectionString = "SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;";
            // con.ConnectionString = "SERVER=.;DATABASE=Best003;PWD=sa;UID=sa;";
            con.Open();
            /*
            SqlDataAdapter 对象。 用于填充DataSet （数据集）。
            SqlDataReader 对象。 从数据库中读取流..
            后面要做增删改查还需要用到 DataSet 对象。
            */

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "update oitm set u_sysflag = 0 where itemcode = '" + itemcode + "'";
            SqlDataReader dr = com.ExecuteReader();//执行SQL语句
            dr.Close();//关闭执行
            con.Close();//关闭数据库
        }
        private void WriteLog(string error, string filepath)
        {
            StreamWriter sw = new StreamWriter(filepath);
            string w = error;
            sw.Write(w);
            sw.Close();
        }
        private void WriteLogNew(string date, string filepath)
        {
            if (File.Exists(filepath))
            {//如果存在则删除
                File.Delete(filepath);
            }
            StreamWriter sw = new StreamWriter(filepath);
            string w = date;
            sw.Write(w);
            sw.Close();
        }
        public string Read(string path)
        {
            if (File.Exists(path))
            {
                StreamReader sr = new StreamReader(path, Encoding.Default);
                String line;
                while ((line = sr.ReadLine()) != null)
                {
                    sr.Close();
                    return line.ToString();
                }
            }

            return null;
        }
        private void SysMJReport()
        {
            string oOAconstr = "SERVER=192.168.2.195;DATABASE=A8;PWD=admin!123;UID=sa;";
            string oSAPconstr = "SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;";
            SynKCJGB_PP(oSAPconstr, oOAconstr);
            SynKCJGB_CPXL(oSAPconstr, oOAconstr);
            SynKCJGB_WLZ(oSAPconstr, oOAconstr);
            SynWLLRB(oSAPconstr, oOAconstr);
            SynPPLRB(oSAPconstr, oOAconstr);
            string path = AppDomain.
CurrentDomain.BaseDirectory + @"\MJReportLog" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt";
            WriteLog("执行一次同步报表！", path);

        }

        /// <summary>
        /// formmain_2085
        ///field0001（品牌）
        ///field0002（销售年份）
        ///field0003（销售月份）
        ///field0004（最终成交价）
        /// field0005（成本）
        /// field0006（利润）
        ///field0007 （利润率）
        /// </summary>
        /// <param name="SAPconstr"></param>
        /// <param name="OAconstr"></param>
        private void SynPPLRB(string SAPconstr, string OAconstr)
        {
            string QuerySAP =
                "SELECT ttt.品牌,ttt.销售年份,ttt.销售月份,sum(ttt.最终成交总价) as '最终成交价',sum(ttt.物料总成本) as '物料总成本' ,sum(ttt.利润) as '利润',case when sum(ttt.物料总成本) = 0  then 'Err' else cast((sum(ttt.利润)/sum(ttt.物料总成本))*100  AS varchar) END AS '利润率(%)' FROM ( SELECT tt.品牌,tt.销售年份,tt.销售月份,sum(tt.最终成交价) AS '最终成交总价',sum(tt.物料成本) AS '物料总成本',sum(tt.利润) AS '利润' FROM ( SELECT  (select U_F003 from OITM t31 where t31.ItemCode=t11.ItemCode ) as '品牌',(select t33.GroupName from  OCRG t33  where t2.GroupCode=t33.GroupCode) as '客户类型',t1.CardCode	as '客户编号',t1.CardName	as '客户名称',t11.ItemCode	as '物料编号',t11.Dscription as '物料描述',t1.DocDate		as '销售日期',year(t1.DocDate)	as '销售年份',month(t1.DocDate)	as '销售月份',t11.Price*t11.Quantity AS '最终成交价',t11.StockPrice*t11.Quantity AS '物料成本',(t11.Price-t11.StockPrice)*t11.Quantity as '利润' FROM OINV t1 INNER join  INV1 t11	on t1.DocEntry=t11.DocEntry inner join  OCRD t2	on t2.CardCode=t1.CardCode	where (t2.GroupCode =105 or t2.GroupCode=103 or t2.GroupCode=112)  " +
                "UNION ALL " +
                "SELECT (select U_F003 from OITM t31 where t31.ItemCode=t11.ItemCode ) as '品牌'		,(select t33.GroupName from  OCRG t33  where t2.GroupCode=t33.GroupCode) as '客户类型'		,t1.CardCode	as '客户编号'		,t1.CardName	as '客户名称'		,t11.ItemCode	as '物料编号'		,t11.Dscription as '物料描述'		,t1.DocDate		as '销售日期'		,year(t1.DocDate)	as '销售年份'		,month(t1.DocDate)	as '销售月份'		,-1*t11.Price*t11.Quantity					 as '最终成交价'		,-1*t11.StockPrice*t11.Quantity			 as '物料成本'		,-1*(t11.Price-t11.StockPrice)*t11.Quantity as '利润'	from ORIN t1	inner join 		 RIN1 t11	on t1.DocEntry=t11.DocEntry inner join 		 OCRD t2	on t2.CardCode=t1.CardCode	where (t2.GroupCode =105 or t2.GroupCode=103 or t2.GroupCode=112) ) tt   group by tt.品牌,tt.销售年份,tt.销售月份,tt.最终成交价) ttt  group by ttt.品牌,ttt.销售年份,ttt.销售月份 ORDER BY ttt.销售年份 DESC,ttt.销售月份 DESC";
            DataTable oDt = GetDtFromSQL(QuerySAP, SAPconstr);
            string DropOADt = "DELETE dbo.formmain_2085 ";
            DoSQL(OAconstr, DropOADt);
            int i = 1;
            foreach (DataRow row in oDt.Rows)
            {
                string InsertSql =
                    "INSERT dbo.formmain_2085( id,field0001 ,field0002 ,field0003 ,field0004 ,field0005,field0006,field0007 ) " +
                    "VALUES  ( " + (i++) + ",N'" + row[0] + "' , N'" + row[1] + "' , N'" + row[2] + "' , N'" + row[3] + "' , N'" +
                    row[4] + "',N'" + row[5] + "',N'" + row[6] + "')";
                DoSQL(OAconstr, InsertSql);
            }
        }

        /// <summary>
        ///formmain_2038（库存结构表（品牌））
        ///field0001（品牌）
        ///field0002（物料编号）
        ///field0003（物料名称）
        ///field0004（库存数量）
        ///field0005（库存单位）
        ///field0006（库存金额）
        ///field0007（物料组）
        ///field0008（产品小类）
        /// </summary>
        /// <param name="SAPconstr"></param>
        /// <param name="OAconstr"></param>
        private void SynKCJGB_PP(string SAPconstr, string OAconstr)
        {
            //string QuerySAP = "SELECT  T0.[U_f003], T2.[ItmsGrpNam], T0.[U_cpxl], sum(T1.[OnHand]) AS 库存数量, sum(T1.[OnHand]*T1.[AvgPrice]) AS 库存金额 FROM OITM T0  INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] left  JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] where T0.[U_f003]<>' ' group by  T0.[U_f003], T2.[ItmsGrpNam], T0.[U_cpxl]";
            string QuerySAP = "SELECT T0.[U_f003], T2.[ItmsGrpNam], T0.[U_cpxl], sum(T1.[OnHand]) AS 库存数量, sum(T1.[OnHand]*T1.[AvgPrice]) AS 库存金额,T0.[ItemCode], T0.[ItemName],  T0.[InvntryUom] FROM OITM T0  INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] left  JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] group by T0.[ItemCode], T0.[ItemName], T0.[U_f003], T2.[ItmsGrpNam], T0.[U_cpxl], T0.[InvntryUom]";

            DataTable oDt = GetDtFromSQL(QuerySAP, SAPconstr);
            string DropOADt = "DELETE dbo.formmain_2038 ";
            DoSQL(OAconstr, DropOADt);
            int i = 1;
            foreach (DataRow row in oDt.Rows)
            {
                string InsertSql =
                    "INSERT dbo.formmain_2038( id,field0001 ,field0007 ,field0008 ,field0004 ,field0006,field0002,field0003,field0005 ) " +
                    "VALUES  ( " + (i++) + ",N'" + row[0] + "' , N'" + row[1] + "' , N'" + row[2] + "' , N'" + row[3] + "' , N'" +
                    row[4] + "',N'" + row[5] + "',N'" + row[6] + "',N'" + row[7] + "')";
                DoSQL(OAconstr, InsertSql);
            }
        }
        /// <summary>
        /// formmain_2037（库存结构表（产品小类））
        ///field0001（产品小类）
        ///field0002（物料编号）
        ///field0003（物料名称）
        ///field0004（库存数量）
        ///field0005（库存单位）
        ///field0006（库存金额）
        ///field0007（物料组）
        ///field0008（品牌）
        /// </summary>
        /// <param name="SAPconstr"></param>
        /// <param name="OAconstr"></param>
        private void SynKCJGB_CPXL(string SAPconstr, string OAconstr)
        {
            string QuerySAP = "SELECT T0.[U_cpxl], sum(T1.[OnHand]) AS 库存数量, sum(T1.[OnHand]*T1.[AvgPrice]) AS 库存金额 FROM OITM T0  INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] where T0.[U_cpxl]<>' ' group by T0.[U_cpxl]";
            DataTable oDt = GetDtFromSQL(QuerySAP, SAPconstr);
            string DropOADt = "DELETE dbo.formmain_2037 ";
            DoSQL(OAconstr, DropOADt);
            int i = 1;
            foreach (DataRow row in oDt.Rows)
            {
                string InsertSql =
                    "INSERT dbo.formmain_2037(id, field0001 ,field0004 ,field0006 ) " +
                    "VALUES  ( " + (i++) + ",N'" + row[0] + "' , N'" + row[1] + "' , N'" + row[2] + "')";
                DoSQL(OAconstr, InsertSql);
            }
        }

        /// <summary>
        /// formmain_2039（库存结构表（物料组））
        ///field0001（物料组）
        ///field0002（物料编号）
        ///field0003（物料名称）
        ///field0004（库存数量）
        ///field0005（库存单位）
        ///field0006（库存金额）
        ///field0007（产品小类）
        ///field0008（品牌）
        /// </summary>
        /// <param name="SAPconstr"></param>
        /// <param name="OAconstr"></param>
        private void SynKCJGB_WLZ(string SAPconstr, string OAconstr)
        {
            string QuerySAP = "SELECT T2.[ItmsGrpNam], sum(T1.[OnHand]) AS 库存数量, sum(T1.[AvgPrice]*T1.[OnHand]) AS 库存金额 FROM OITM T0  left JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] left  JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] where T2.[ItmsGrpNam]<>' ' group by T2.[ItmsGrpNam]";
            DataTable oDt = GetDtFromSQL(QuerySAP, SAPconstr);
            string DropOADt = "DELETE dbo.formmain_2039 ";
            DoSQL(OAconstr, DropOADt);
            int i = 1;
            foreach (DataRow row in oDt.Rows)
            {
                string InsertSql =
                    "INSERT dbo.formmain_2039(id, field0001 ,field0004 ,field0006 ) " +
                    "VALUES  ( " + (i++) + ",N'" + row[0] + "' , N'" + row[1] + "' , N'" + row[2] + "')";
                DoSQL(OAconstr, InsertSql);
            }
        }
        /// <summary>
        /// formmain_2033（物料利润分析表）
        ///field0001（品牌）
        ///field0002（客户类型）
        ///field0003（客户编号）
        ///field0004（客户名称）
        ///field0005（物料编号）
        ///field0006（物料描述）
        ///field0007（销售日期）
        ///field0008（物料成本）
        ///field0009（最终成交价）
        ///field00010（利润）
        ///field00011（利润率）
        ///field00012（销售年）
        ///field00013（销售月）
        /// </summary>
        /// <param name="SAPconstr"></param>
        /// <param name="OAconstr"></param>
        private void SynWLLRB(string SAPconstr, string OAconstr)
        {
            string QuerySAP = "select tt.品牌,tt.客户类型,tt.客户编号,tt.客户名称,tt.物料编号,tt.物料描述,tt.销售年份,tt.销售月份,tt.销售日期 " +
                              ",sum(tt.最终成交价) as '最终成交总价',sum(tt.物料成本)	as '物料总成本',sum(tt.利润) as '利润',case when sum(tt.物料成本) = 0  then 'Err' else cast((sum(tt.利润)/sum(tt.物料成本))*100	 as varchar)	end as '利润率(%)' " +
                              "from (select (select U_F003 from OITM t31 where t31.ItemCode=t11.ItemCode ) as '品牌',(select t33.GroupName from  OCRG t33  where t2.GroupCode=t33.GroupCode) as '客户类型',t1.CardCode	as '客户编号',t1.CardName	as '客户名称',t11.ItemCode	as '物料编号',t11.Dscription as '物料描述',t1.DocDate		as '销售日期',year(t1.DocDate)	as '销售年份',month(t1.DocDate)	as '销售月份',t11.Price*t11.Quantity as '最终成交价' " +
                              ",t11.StockPrice*t11.Quantity as '物料成本' " +
                              ",(t11.Price-t11.StockPrice)*t11.Quantity as '利润' " +
                              "from OINV t1	inner join " +
                              "INV1 t11	on t1.DocEntry=t11.DocEntry inner join " +
                              "OCRD t2	on t2.CardCode=t1.CardCode  " +
                              "where (t2.GroupCode =105 or t2.GroupCode=103 or t2.GroupCode=112) " +
                              "union all " +
                              "select (select U_F003 from OITM t31 where t31.ItemCode=t11.ItemCode ) as '品牌',(select t33.GroupName from  OCRG t33  where t2.GroupCode=t33.GroupCode) as '客户类型',t1.CardCode	as '客户编号',t1.CardName	as '客户名称',t11.ItemCode	as '物料编号',t11.Dscription as '物料描述',t1.DocDate as '销售日期',year(t1.DocDate)	as '销售年份',month(t1.DocDate)	as '销售月份',-1*t11.Price*t11.Quantity	 as '最终成交价' " +
                              ",-1*t11.StockPrice*t11.Quantity as '物料成本' " +
                              ",-1*(t11.Price-t11.StockPrice)*t11.Quantity as '利润' " +
                              "from ORIN t1	inner join  " +
                              "RIN1 t11	on t1.DocEntry=t11.DocEntry inner join " +
                              "OCRD t2	on t2.CardCode=t1.CardCode " +
                              "where (t2.GroupCode =105 or t2.GroupCode=103 or t2.GroupCode=112)  " +
                              ") tt " +
                              "group by tt.品牌,tt.客户类型,tt.客户编号,tt.客户名称,tt.物料编号,tt.物料描述,tt.销售年份,tt.销售月份,tt.销售日期,tt.最终成交价 "
                              + " order by tt.销售日期 desc";
            DataTable oDt = GetDtFromSQL(QuerySAP, SAPconstr);
            string DropOADt = "DELETE dbo.formmain_2033 ";
            DoSQL(OAconstr, DropOADt);
            int i = 1;
            foreach (DataRow row in oDt.Rows)
            {
                string InsertSql =
                    "INSERT dbo.formmain_2033(id, field0001 ,field0002 ,field0003,field0004,field0005,field0006,field0012,field0013,field0007,field0009,field0008,field0010,field0011 ) " +
                    "VALUES  (" + (i++) + ", N'" + row[0] + "' , N'" + row[1] + "' , N'" + row[2] + "' , N'" + row[3] + "' , N'" +
                    row[4] + "',N'" + row[5] + "',N'" + row[6] + "',N'" + row[7] + "',N'" + row[8] + "',N'" + row[9] +
                    "',N'" + row[10] + "',N'" + row[11] + "',N'" + row[12] + "')";
                DoSQL(OAconstr, InsertSql);
            }
        }
        private void DoSQL(string ConStr, string oSQL)
        {
            //先打开两个类库文件
            SqlConnection con = new SqlConnection();
            //  con.ConnectionString = "server=505-03;database=ttt;user=sa;pwd=123";
            con.ConnectionString = ConStr;
            // con.ConnectionString = "SERVER=192.168.2.23;DATABASE=MJ_Sales_SBODB;PWD=abcd=1234;UID=sa;";
            // con.ConnectionString = "SERVER=.;DATABASE=Best003;PWD=sa;UID=sa;";
            con.Open();
            /*
            SqlDataAdapter 对象。 用于填充DataSet （数据集）。
            SqlDataReader 对象。 从数据库中读取流..
            后面要做增删改查还需要用到 DataSet 对象。
            */
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = oSQL;
            SqlDataReader dr = com.ExecuteReader();//执行SQL语句
            dr.Close();//关闭执行
            con.Close();//关闭数据库
        }
    }
}
